//
//  YNUserGroupRelations.m
//  Messenger
//
//  Created by OC on 15/1/19.
//  Copyright (c) 2015年 李健. All rights reserved.
//
//  用户与群/讨论组关系实体类 groupType:1 群  groupType:2 讨论组
//

#import "YNContactGroupRelation.h"
#import "YNUser.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "FMDatabaseAdditions.h"
#import "XMPPDataSecurity.h"
#import "YNIMTool.h"
#import "YNIMFrameworkMacro.h"

@implementation YNContactGroupRelation

+ (NSString *)ID
{
    return @"ID";
}

+ (NSString *)userNo
{
    return @"userNo";
}

+ (NSString *)groupName
{
    return @"groupName";
}

+ (NSString *)role
{
    return @"role";
}

+ (NSString *)tableName
{
    return @"GroupRelations";
    
}

- (NSArray *)insertParam
{
    return @[[YNIMTool nilFilter:self.userNo],
             [YNIMTool nilFilter:self.groupName],
             [YNIMTool nilFilter:self.role]];
}

- (NSArray *)updateParam
{
    return @[[YNIMTool nilFilter:self.role],
             [YNIMTool nilFilter:self.groupName],
             [YNIMTool nilFilter:self.userNo]];
}

//KVC 将jid的值赋给userNo
- (void)setValue:(id)value forUndefinedKey:(NSString *)key
{
    if ([key isEqualToString:@"jid"])
    {
        NSArray *userNoArray = [value componentsSeparatedByString:@"@"];
        self.userNo = [userNoArray firstObject];
    }
}

//创建用户与群/讨论组关系表
+ (BOOL)createWithGroupDatabase:(FMDatabase *)db
{
    if ([db tableExists:[YNContactGroupRelation tableName]])
    {
        return YES;
    }
    NSString *createContactGroupRelationsSql = [NSString stringWithFormat:@"%@%@(%@ INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %@ varchar(36), %@ varchar(36), %@ varchar(16))",CREATE_TABLE,
                                                [YNContactGroupRelation tableName],
                                                [YNContactGroupRelation ID],
                                                [YNContactGroupRelation userNo],
                                                [YNContactGroupRelation groupName],
                                                [YNContactGroupRelation role]];
    BOOL result = [db executeUpdate:createContactGroupRelationsSql];
    return result;
}
//插入用户与群/讨论组关系
- (BOOL)insertWithGroupDatabase:(FMDatabase *)db
{
    
    NSString *fieldString = [NSString stringWithFormat:@"%@,%@,%@",
                             [YNContactGroupRelation userNo],
                             [YNContactGroupRelation groupName],
                             [YNContactGroupRelation role]];
    
    
    NSString *insertContactGroupRelationsSql = [NSString stringWithFormat:@"%@%@ (%@) VALUES (?,?,?)",INSERT_TABLE,[YNContactGroupRelation tableName],fieldString];
    
    BOOL result = [db executeUpdate:insertContactGroupRelationsSql withArgumentsInArray:[self insertParam]];
    
    return result;
}

//根据gruoupName查询用户
+ (NSArray *)queryByGruoupName:(NSString *)groupName database:(FMDatabase *)db isAdmin:(BOOL)isAdmin onlyGetTeachers:(BOOL)onlyTeacher
{
    NSString *conjunctiveQuerySql;
    
    if (isAdmin)
    {
        if (onlyTeacher) {
            conjunctiveQuerySql= [NSString stringWithFormat:@"SELECT u.* FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND u.userType = 1 AND rel.role = %@",[YNContactGroupRelation tableName],@"10"];
        }else{
            conjunctiveQuerySql= [NSString stringWithFormat:@"SELECT u.* FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND rel.role = %@",[YNContactGroupRelation tableName],@"10"];
        }
    }
    else
    {
        if (onlyTeacher) {
            conjunctiveQuerySql = [NSString stringWithFormat:@"SELECT u.* FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND u.userType = 1",[YNContactGroupRelation tableName]];
        }else{
            conjunctiveQuerySql = [NSString stringWithFormat:@"SELECT u.* FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 ",[YNContactGroupRelation tableName]];
        }
    }
    
    
    FMResultSet *rs = [db executeQuery:conjunctiveQuerySql,groupName];
    
    NSMutableArray *userArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        user.telephone=[XMPPDataSecurity dataDecryptionBy:user.telephone];
        user.email=[XMPPDataSecurity dataDecryptionBy:user.email];
        [userArray addObject:user];
    }
    
    [rs close];
    return userArray;
}


//根据gruoupName查询用户的在线人数
+(NSInteger)queryUserOnlineCountByGruoupName:(NSString *)groupName database:(FMDatabase *)db isAdmin:(BOOL)isAdmin onlyGetTeachers:(BOOL)onlyTeacher
{
    NSString *conjunctiveQuerySql;
    
    if (isAdmin)
    {
        if (onlyTeacher) {
            conjunctiveQuerySql= [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND u.userType = 1 AND rel.role = %@ AND (u.pcStatus = 'online' OR u.phoneStatus = 'online')",[YNContactGroupRelation tableName],@"10"];
        }else{
            conjunctiveQuerySql= [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND rel.role = %@ AND (u.pcStatus = 'online' OR u.phoneStatus = 'online')",[YNContactGroupRelation tableName],@"10"];
        }
    }
    else
    {
        if (onlyTeacher) {
            conjunctiveQuerySql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND u.userType = 1 AND (u.pcStatus = 'online' OR u.phoneStatus = 'online')",[YNContactGroupRelation tableName]];
        }else{
            conjunctiveQuerySql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ rel,User u WHERE rel.userNo = u.userNo AND rel.groupName = ? AND u.removeTag = 0 AND (u.pcStatus = 'online' OR u.phoneStatus = 'online')",[YNContactGroupRelation tableName]];
        }
        
        
    }
    
    NSInteger onlineCount = [db intForQuery:conjunctiveQuerySql,groupName];
    
    return onlineCount;
}

/**
 查询自己在群组里的角色（是否是管理员）

 @param groupType
 @param db
 @return 结果集
 */
+ (NSArray<NSDictionary*> *)queryMyGroupRoleWithGroupId:(NSString*)groupId database:(FMDatabase *)db
{
    NSString*userNO=[YNIMmanager sharedInstance].userImId;
    if ([YNIMTool isEmptyString:userNO]) {
        userNO=@"";
    }
    NSString* conjunctiveQuerySql= [NSString stringWithFormat:@"SELECT %@ FROM %@ WHERE %@ = '%@' AND %@ = '%@'",[YNContactGroupRelation role],[YNContactGroupRelation tableName],[YNContactGroupRelation userNo],userNO,[YNContactGroupRelation groupName],groupId];
    
    FMResultSet *rs = [db executeQuery:conjunctiveQuerySql];
    
    NSMutableArray *roleArray = [NSMutableArray array];
    
    while ([rs next])
    {
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [roleArray addObject:[rs resultDictionary]];
        }
    }
    
    [rs close];
    return roleArray;
}


//根据gruoupName和userNo 查询用户与群/讨论组关系
- (NSArray *)queryWithGroupDatabase:(FMDatabase *)db
{
    NSString *queryContactGroupRelationsSql =  [NSString stringWithFormat:@"SELECT *FROM %@ WHERE %@ = ? AND %@ = ?",
                                                [YNContactGroupRelation tableName],
                                                [YNContactGroupRelation groupName],
                                                [YNContactGroupRelation userNo]];
    
    FMResultSet *rs = [db executeQuery:queryContactGroupRelationsSql,self.groupName,self.userNo];
    
    NSMutableArray *groupRelArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNContactGroupRelation *groupRel = [[YNContactGroupRelation alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [groupRel setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        [groupRelArray addObject:groupRel];
    }
    
    [rs close];
    
    return groupRelArray;
}
//更新用户与群/讨论组关系
- (BOOL)updateWithGroupDatabase:(FMDatabase *)db
{
    NSString *updateContactGroupRelationsSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = ?WHERE %@ = ? AND %@ = ? ",
                                                [YNContactGroupRelation tableName],
                                                [YNContactGroupRelation role],
                                                [YNContactGroupRelation groupName],
                                                [YNContactGroupRelation userNo]];
    
    BOOL result = [db executeUpdate:updateContactGroupRelationsSql withArgumentsInArray:[self updateParam]];
    return result;
}


//删除表中指定群类型所有数据
+ (BOOL)deleteAllDataWithGroupType:(CurrenChatObjType)groupType database:(FMDatabase *)db
{
    NSString *deleteContactGroupSql = @"";
    if (groupType == CurrenChatObjType_group) {
        deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ LIKE '%@%%'",[YNContactGroupRelation tableName],[YNContactGroupRelation groupName],UserGroupFlag];
    }else if (groupType == CurrenChatObjType_discussion){
        deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ LIKE '%@%%'",[YNContactGroupRelation tableName],[YNContactGroupRelation groupName],DiscussionGroupFlag];
    }else if (groupType == CurrenChatObjType_projectTeam){
        deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ LIKE '%@%%'",[YNContactGroupRelation tableName],[YNContactGroupRelation groupName],ProjectTeamFlag];
    }else{
        deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@",[YNContactGroupRelation tableName]];
    }
    //删除自增字段数据
    NSString *deleteAutoFieldSql = [NSString stringWithFormat:@"UPDATE sqlite_sequence SET seq = 0 WHERE name='%@'",[YNContactGroupRelation tableName]];
    
    BOOL result = [db executeUpdate:deleteContactGroupSql];
    
    if (result)
    {
        result = [db executeUpdate:deleteAutoFieldSql];
    }
    
    return result;
}

//删除群/讨论组所有成员关系
+ (BOOL)deleteByGroupName:(NSString *)groupName database:(FMDatabase *)db
{
    NSString *deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",[YNContactGroupRelation tableName],[YNContactGroupRelation groupName]];
    
    BOOL result = [db executeUpdate:deleteContactGroupSql,groupName];
    
    return result;
}

//删除某个成员与群/讨论组关系
+ (BOOL)deleteByGroupName:(NSString *)groupName andUserNo:(NSString *)userNo database:(FMDatabase *)db
{
    NSString *deleteContactGroupSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ? AND %@ = ?",[YNContactGroupRelation tableName],
                                       [YNContactGroupRelation groupName],
                                       [YNContactGroupRelation userNo]];
    BOOL result = [db executeUpdate:deleteContactGroupSql,groupName,userNo];
    
    return result;
}

/**
 *  @author 李健, 15-06-25
 *
 *  @brief  查询讨论组创建者
 *
 *  @param db
 *
 *  @return 存放讨论组与成员的关系人员id
 */
+ (NSArray<NSString*> *)queryCreatorOfDiscussionGroupInDatabase:(FMDatabase *)db
{
     NSString *queryCreatorSql =  [NSString stringWithFormat:@"SELECT *FROM %@ WHERE %@ = ?",
                                                [YNContactGroupRelation tableName],
                                                [YNContactGroupRelation role]];
    
    FMResultSet *rs = [db executeQuery:queryCreatorSql,@"10"];
    
    NSMutableArray *creatorArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNContactGroupRelation *groupRel = [[YNContactGroupRelation alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [groupRel setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        ///将讨论组数据筛选出来
        if ([groupRel.groupName rangeOfString:DiscussionGroupFlag].length > 0) {
            [creatorArray addObject:groupRel.userNo];
        }
    }
    
    [rs close];
    
    return creatorArray;

}



//查询ContactGroupRelations是否存在
- (BOOL)isContactGroupRelExistInDatabase:(FMDatabase *)db
{
    NSArray *groupRelArray = [self queryWithGroupDatabase:db];
    if (groupRelArray.count > 0)
    {
        return YES;
    }
    
    return NO;
}

@end
